<!DOCTYPE html>
<!--[if IE 8]><html class="no-js lt-ie9" lang="en" > <![endif]-->
<!--[if gt IE 8]><!-->
<html class="no-js" lang="en">
<!--<![endif]-->
<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">

    <title>Discover Tables and Columns - The .NET Core ORM Cookbook</title>
    <link rel="shortcut icon" href="favicon.ico">
    <link rel="stylesheet" href="css/theme.css" type="text/css" />
    <link rel="stylesheet" href="css/theme_colors.css" type="text/css" />
    <link rel="stylesheet" href="css/styles/vs.css">
    <link rel="stylesheet" href="css/font-awesome.4.5.0.min.css">
</head>
<body role="document">
    <div class="grid-for-nav">
        <nav data-toggle="nav-shift" class="nav-side stickynav">
            <div class="side-nav-search">
                <a href="index.htm"><i class="fa fa-home"></i> The .NET Core ORM Cookbook</a>
                <div role="search">
                    <form id="search-form" class="form" action="Docnet_search.htm" method="get">
                        <input type="text" name="q" placeholder="Search docs" />
                    </form>
                </div>
            </div>
            <div class="menu menu-vertical" data-spy="affix" role="navigation" aria-label="main navigation">
<ul>
<li class="tocentry"><a href="index.htm">Home</a>
</li>

<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="ORMs.htm">ORMs</a></span>
</li>
<li class="tocentry"><a href="FAQ.htm">FAQ</a>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="StandardCRUDscenarios.htm">Standard CRUD scenarios</a></span>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="Fetchingdatascenarios.htm">Fetching data scenarios</a></span>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="Advancedscenarios.htm">Advanced scenarios</a></span>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="Sortingscenarios.htm">Sorting scenarios</a></span>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="Storedprocedurescenarios.htm">Stored procedure scenarios</a></span>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="Auditingandhistoryscenarios.htm">Auditing and history scenarios</a></span>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="Multi-Tenancyscenarios.htm">Multi-Tenancy scenarios</a></span>
</li>
<li class="tocentry">
<ul>
<li><span class="navigationgroup"><i class="fa fa-caret-down"></i> <a href="UnknownDatabasescenarios.htm">Unknown Database scenarios</a></span></li>
<li class="tocentry current"><a class="current" href="DiscoverTablesAndColumns.htm">Discover Tables and Columns</a>
<ul class="currentrelative">
<li class="tocentry"><a href="#scenario-prototype">Scenario Prototype</a></li>

<li class="tocentry"><a href="#ado.net">ADO.NET</a></li>

<li class="tocentry"><a href="#chain">Chain</a></li>

<li class="tocentry"><a href="#dapper">Dapper</a></li>

<li class="tocentry"><a href="#dbconnector">DbConnector</a></li>

<li class="tocentry"><a href="#entity-framework-6">Entity Framework 6</a></li>

<li class="tocentry"><a href="#entity-framework-core">Entity Framework Core</a></li>

<li class="tocentry"><a href="#linq-to-db">LINQ to DB</a></li>

<li class="tocentry"><a href="#llblgen-pro">LLBLGen Pro</a></li>

<li class="tocentry"><a href="#nhibernate">NHibernate</a></li>

<li class="tocentry"><a href="#repodb">RepoDb</a></li>

<li class="tocentry"><a href="#servicestack">ServiceStack</a></li>



</ul>
<li class="tocentry"><a href="ArbitraryTableRead.htm">Read from an Arbitrary Table</a>
</li>

</ul>
</li>
</ul>
				<div class="toc-footer">
					<span class="text-small">
						<hr/>
						<a href="https://github.com/FransBouma/DocNet" target="_blank">Made with <i class="fa fa-github"></i> DocNet</a>
					</span>
				</div>	
			</div>
            &nbsp;
        </nav>
        <section data-toggle="nav-shift" class="nav-content-wrap">
            <nav class="nav-top" role="navigation" aria-label="top navigation">
                <i data-toggle="nav-top" class="fa fa-bars"></i>
                <a href="index.htm">The .NET Core ORM Cookbook</a>
            </nav>
            <div class="nav-content">
                <div role="navigation" aria-label="breadcrumbs navigation">
                    <div class="breadcrumbs">
<ul><li><a href="index.htm">Home</a></li> / <li><a href="UnknownDatabasescenarios.htm">Unknown Database scenarios</a></li> / <li><a href="DiscoverTablesAndColumns.htm">Discover Tables and Columns</a></li></ul>
					
                    </div>
                    <hr />
                </div>
                <div role="main">
                    <div class="section">
<h1 id="discover-tables-and-columns">Discover Tables and Columns<a class="headerlink" href="#discover-tables-and-columns" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h1>
<p>These scenarios demonstrate how to list the tables and columns in an unknown database. </p>
<h2 id="scenario-prototype">Scenario Prototype<a class="headerlink" href="#scenario-prototype" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<pre><code class="cs">public interface IDiscoverTablesAndColumnsScenario
{
    IList&lt;string&gt; ListColumnsInTable(string schemaName, string tableName);

    IList&lt;string&gt; ListColumnsInView(string schemaName, string viewName);

    IList&lt;string&gt; ListTables();

    IList&lt;string&gt; ListViews();
}
</code></pre>

<h2 id="ado.net">ADO.NET<a class="headerlink" href="#ado.net" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<p>The SQL needed to list tables, views, and columns is database-specific. </p>
<ul>
<li>DB2: <a href="https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/cattab/src/tpc/db2z_catalogtablesintro.html" target="_blank">Catalog Tables</a></li>
<li>MySQL: <a href="https://dev.mysql.com/doc/refman/5.7/en/information-schema.html" target="_blank">Information Schema</a></li>
<li>Oracle: <a href="https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/oracle-schema-collections" target="_blank">Information Schema</a></li>
<li>PostgreSQL: <a href="https://www.postgresql.org/docs/9.1/information-schema.html" target="_blank">Information Schema</a></li>
<li>SQL Server: <a href="">System Views</a> or <a href="https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-schema-collections" target="_blank">Information Schema</a></li>
</ul>
<div class="alert alert-warning"><span class="alert-title"><i class="fa fa-warning"></i> Warning!</span><p>While most databases expose Information Schema (e.g. <code>INFORMATION_SCHEMA.TABLES</code>), the column names may vary from vendor to vendor.</p>
</div><pre><code class="cs">public class DiscoverTablesAndColumnsScenario : SqlServerScenarioBase, IDiscoverTablesAndColumnsScenario
{
    public DiscoverTablesAndColumnsScenario(string connectionString) : base(connectionString)
    { }

    public IList&lt;string&gt; ListColumnsInTable(string schemaName, string tableName)
    {
        const string sql =
            @&quot;SELECT c.name FROM sys.columns c
                INNER JOIN sys.tables t ON c.object_id = t.object_id
                INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
                WHERE s.name = @SchemaName AND t.name = @TableName&quot;;

        var result = new List&lt;string&gt;();
        using (var con = OpenConnection())
        using (var cmd = new SqlCommand(sql, con))
        {
            cmd.Parameters.AddWithValue(&quot;@SchemaName&quot;, schemaName);
            cmd.Parameters.AddWithValue(&quot;@TableName&quot;, tableName);
            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                    result.Add(reader.GetString(0));
            }
        }
        return result;
    }

    public IList&lt;string&gt; ListColumnsInView(string schemaName, string viewName)
    {
        const string sql =
            @&quot;SELECT c.name FROM sys.columns c
                INNER JOIN sys.views v ON c.object_id = v.object_id
                INNER JOIN sys.schemas s ON v.schema_id = s.schema_id
                WHERE s.name = @SchemaName AND v.name = @ViewName&quot;;

        var result = new List&lt;string&gt;();
        using (var con = OpenConnection())
        using (var cmd = new SqlCommand(sql, con))
        {
            cmd.Parameters.AddWithValue(&quot;@SchemaName&quot;, schemaName);
            cmd.Parameters.AddWithValue(&quot;@ViewName&quot;, viewName);
            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                    result.Add(reader.GetString(0));
            }
        }
        return result;
    }

    public IList&lt;string&gt; ListTables()
    {
        const string sql =
            &quot;SELECT s.name + '.' + t.name FROM sys.tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id&quot;;

        var result = new List&lt;string&gt;();
        using (var con = OpenConnection())
        using (var cmd = new SqlCommand(sql, con))
        {
            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                    result.Add(reader.GetString(0));
            }
        }
        return result;
    }

    public IList&lt;string&gt; ListViews()
    {
        const string sql =
            &quot;SELECT s.name + '.' + v.name  FROM sys.views v INNER JOIN sys.schemas s ON v.schema_id = s.schema_id&quot;;

        var result = new List&lt;string&gt;();
        using (var con = OpenConnection())
        using (var cmd = new SqlCommand(sql, con))
        {
            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                    result.Add(reader.GetString(0));
            }
        }
        return result;
    }
}
</code></pre>

<h2 id="chain">Chain<a class="headerlink" href="#chain" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<p>Chain exposes information about the database via the <code>DatabaseMetadata</code> property. If you don't use the <code>Preload</code> method, only tables and views previously seen will be available.</p>
<pre><code class="cs">public class DiscoverTablesAndColumnsScenario : IDiscoverTablesAndColumnsScenario
{
    readonly SqlServerDataSource m_DataSource;

    public DiscoverTablesAndColumnsScenario(SqlServerDataSource dataSource)
    {
        m_DataSource = dataSource;
    }

    public IList&lt;string&gt; ListColumnsInTable(string schemaName, string tableName)
    {
        return m_DataSource.DatabaseMetadata.GetTableOrView(schemaName + &quot;.&quot; + tableName)
            .Columns.Select(c =&gt; c.SqlName).ToList();
    }

    public IList&lt;string&gt; ListColumnsInView(string schemaName, string viewName)
    {
        return m_DataSource.DatabaseMetadata.GetTableOrView(schemaName + &quot;.&quot; + viewName)
            .Columns.Select(c =&gt; c.SqlName).ToList();
    }

    public IList&lt;string&gt; ListTables()
    {
        m_DataSource.DatabaseMetadata.PreloadTables(); //Only need to call this once
        return m_DataSource.DatabaseMetadata.GetTablesAndViews().Where(t =&gt; t.IsTable)
            .Select(t =&gt; t.Name.ToString()).ToList();
    }

    public IList&lt;string&gt; ListViews()
    {
        m_DataSource.DatabaseMetadata.PreloadViews(); //Only need to call this once
        return m_DataSource.DatabaseMetadata.GetTablesAndViews().Where(t =&gt; t.IsTable == false)
            .Select(t =&gt; t.Name.ToString()).ToList();
    }
}
</code></pre>

<h2 id="dapper">Dapper<a class="headerlink" href="#dapper" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<p>The SQL needed to list tables, views, and columns is database-specific. See the ADO.NET example above for links to the documentation. </p>
<pre><code class="cs">public class DiscoverTablesAndColumnsScenario : ScenarioBase, IDiscoverTablesAndColumnsScenario
{
    public DiscoverTablesAndColumnsScenario(string connectionString) : base(connectionString)
    { }

    public IList&lt;string&gt; ListColumnsInTable(string schemaName, string tableName)
    {
        const string sql =
            @&quot;SELECT c.name FROM sys.columns c
                INNER JOIN sys.tables t ON c.object_id = t.object_id
                INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
                WHERE s.name = @SchemaName AND t.name = @TableName&quot;;

        using (var con = OpenConnection())
            return con.Query&lt;string&gt;(sql, new { schemaName, tableName }).ToList();
    }

    public IList&lt;string&gt; ListColumnsInView(string schemaName, string viewName)
    {
        const string sql =
            @&quot;SELECT c.name FROM sys.columns c
                INNER JOIN sys.views v ON c.object_id = v.object_id
                INNER JOIN sys.schemas s ON v.schema_id = s.schema_id
                WHERE s.name = @SchemaName AND v.name = @ViewName&quot;;

        using (var con = OpenConnection())
            return con.Query&lt;string&gt;(sql, new { schemaName, viewName }).ToList();
    }

    public IList&lt;string&gt; ListTables()
    {
        const string sql =
            &quot;SELECT s.name + '.' + t.name FROM sys.tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id&quot;;

        using (var con = OpenConnection())
            return con.Query&lt;string&gt;(sql).ToList();
    }

    public IList&lt;string&gt; ListViews()
    {
        const string sql =
            &quot;SELECT s.name + '.' + v.name  FROM sys.views v INNER JOIN sys.schemas s ON v.schema_id = s.schema_id&quot;;

        using (var con = OpenConnection())
            return con.Query&lt;string&gt;(sql).ToList();
    }
}
</code></pre>

<h2 id="dbconnector">DbConnector<a class="headerlink" href="#dbconnector" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<p>TODO</p>
<h2 id="entity-framework-6">Entity Framework 6<a class="headerlink" href="#entity-framework-6" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<p>TODO</p>
<h2 id="entity-framework-core">Entity Framework Core<a class="headerlink" href="#entity-framework-core" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<p>The SQL needed to list tables, views, and columns is database-specific. See the ADO.NET example above for links to the documentation.</p>
<pre><code class="cs">public class DiscoverTablesAndColumnsScenario : IDiscoverTablesAndColumnsScenario
{
    private readonly Func&lt;OrmCookbookContext&gt; CreateDbContext;

    public DiscoverTablesAndColumnsScenario(Func&lt;OrmCookbookContext&gt; dBContextFactory)
    {
        CreateDbContext = dBContextFactory;
    }

    public IList&lt;string&gt; ListColumnsInTable(string schemaName, string tableName)
    {
        using OrmCookbookContext context = CreateDbContext();
        using DbCommand commnd = context.Database.GetDbConnection().CreateCommand();
        const string sql =
            @&quot;SELECT c.name AS ColumnName
            FROM sys.columns c
            INNER JOIN sys.tables t ON c.object_id = t.object_id
            INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
            WHERE s.name = @SchemaName AND t.name = @TableName&quot;;
        commnd.CommandText = sql;
        commnd.Parameters.Add(new SqlParameter { ParameterName = &quot;SchemaName&quot;, DbType = DbType.String, Value = schemaName });
        commnd.Parameters.Add(new SqlParameter { ParameterName = &quot;TableName&quot;, DbType = DbType.String, Value = tableName });
        commnd.Connection!.Open();
        DbDataReader reader = commnd.ExecuteReader(CommandBehavior.CloseConnection);
        var columnNames = new List&lt;string&gt;();
        while (reader.Read())
        {
            int columnNameOrdinal = reader.GetOrdinal(&quot;ColumnName&quot;);
            string columnName = reader.GetString(columnNameOrdinal);
            columnNames.Add(columnName);
        }
        return columnNames;
    }

    public IList&lt;string&gt; ListColumnsInView(string schemaName, string viewName)
    {
        using OrmCookbookContext context = CreateDbContext();
        using DbCommand commnd = context.Database.GetDbConnection().CreateCommand();
        const string sql =
            @&quot;SELECT c.name AS ColumnName
            FROM sys.columns c
            INNER JOIN sys.views v ON c.object_id = v.object_id
            INNER JOIN sys.schemas s ON v.schema_id = s.schema_id
            WHERE s.name = @SchemaName AND v.name = @ViewName&quot;;
        commnd.CommandText = sql;
        commnd.Parameters.Add(new SqlParameter { ParameterName = &quot;SchemaName&quot;, DbType = DbType.String, Value = schemaName });
        commnd.Parameters.Add(new SqlParameter { ParameterName = &quot;ViewName&quot;, DbType = DbType.String, Value = viewName });
        commnd.Connection!.Open();
        DbDataReader reader = commnd.ExecuteReader(CommandBehavior.CloseConnection);
        var columnNames = new List&lt;string&gt;();
        while (reader.Read())
        {
            int columnNameOrdinal = reader.GetOrdinal(&quot;ColumnName&quot;);
            string columnName = reader.GetString(columnNameOrdinal);
            columnNames.Add(columnName);
        }
        return columnNames;
    }

    public IList&lt;string&gt; ListTables()
    {
        using OrmCookbookContext context = CreateDbContext();
        using DbCommand commnd = context.Database.GetDbConnection().CreateCommand();
        const string sql =
            @&quot;SELECT s.name + '.' + t.name AS TableName
            FROM sys.tables t 
            INNER JOIN sys.schemas s ON t.schema_id = s.schema_id&quot;;
        commnd.CommandText = sql;
        commnd.Connection!.Open();
        DbDataReader reader = commnd.ExecuteReader(CommandBehavior.CloseConnection);
        var tableNames = new List&lt;string&gt;();
        while (reader.Read())
        {
            int tableNameOrdinal = reader.GetOrdinal(&quot;TableName&quot;);
            string tableName = reader.GetString(tableNameOrdinal);
            tableNames.Add(tableName);
        }
        return tableNames;
    }

    public IList&lt;string&gt; ListViews()
    {
        using OrmCookbookContext context = CreateDbContext();
        using DbCommand commnd = context.Database.GetDbConnection().CreateCommand();
        const string sql =
            @&quot;SELECT s.name + '.' + v.name AS ViewName 
            FROM sys.views v 
            INNER JOIN sys.schemas s ON v.schema_id = s.schema_id&quot;;
        commnd.CommandText = sql;
        commnd.Connection!.Open();
        DbDataReader reader = commnd.ExecuteReader(CommandBehavior.CloseConnection);
        var tableNames = new List&lt;string&gt;();
        while (reader.Read())
        {
            int tableNameOrdinal = reader.GetOrdinal(&quot;ViewName&quot;);
            string tableName = reader.GetString(tableNameOrdinal);
            tableNames.Add(tableName);
        }
        return tableNames;
    }
}
</code></pre>

<h2 id="linq-to-db">LINQ to DB<a class="headerlink" href="#linq-to-db" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<pre><code class="cs">public class DiscoverTablesAndColumnsScenario : IDiscoverTablesAndColumnsScenario
{
    public IList&lt;string&gt; ListColumnsInTable(string schemaName, string tableName)
    {
        using (var db = new OrmCookbook())
        {
            var dbSchema = db.DataProvider.GetSchemaProvider().GetSchema(db,
                new GetSchemaOptions()
                {
                    IncludedSchemas = new[] { schemaName },
                    GetForeignKeys = false,
                    GetProcedures = false,
                    GetTables = true,
                    LoadTable = (t) =&gt; t.Schema == schemaName &amp;&amp; t.Name == tableName
                });
            return dbSchema.Tables.Single().Columns.Select(c =&gt; c.ColumnName).ToList();
        }
    }

    public IList&lt;string&gt; ListColumnsInView(string schemaName, string viewName)
    {
        using (var db = new OrmCookbook())
        {
            var dbSchema = db.DataProvider.GetSchemaProvider().GetSchema(db,
                new GetSchemaOptions()
                {
                    IncludedSchemas = new[] { schemaName },
                    GetForeignKeys = false,
                    GetProcedures = false,
                    GetTables = true,
                    LoadTable = (t) =&gt; t.Schema == schemaName &amp;&amp; t.Name == viewName
                });
            return dbSchema.Tables.Single().Columns.Select(c =&gt; c.ColumnName).ToList();
        }
    }

    public IList&lt;string&gt; ListTables()
    {
        using (var db = new OrmCookbook())
        {
            var dbSchema = db.DataProvider.GetSchemaProvider().GetSchema(db,
                new GetSchemaOptions()
                {
                    GetForeignKeys = false,
                    GetProcedures = false,
                    GetTables = true,
                    LoadTable = (t) =&gt; !t.IsView
                });
            return dbSchema.Tables.Select(t =&gt; t.SchemaName + &quot;.&quot; + t.TableName).ToList();
        }
    }

    public IList&lt;string&gt; ListViews()
    {
        using (var db = new OrmCookbook())
        {
            var dbSchema = db.DataProvider.GetSchemaProvider().GetSchema(db,
                new GetSchemaOptions()
                {
                    GetForeignKeys = false,
                    GetProcedures = false,
                    GetTables = true,
                    LoadTable = (t) =&gt; t.IsView
                });
            return dbSchema.Tables.Select(t =&gt; t.SchemaName + &quot;.&quot; + t.TableName).ToList();
        }
    }
}
</code></pre>

<h2 id="llblgen-pro">LLBLGen Pro<a class="headerlink" href="#llblgen-pro" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<p>TODO</p>
<h2 id="nhibernate">NHibernate<a class="headerlink" href="#nhibernate" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<p>TODO</p>
<h2 id="repodb">RepoDb<a class="headerlink" href="#repodb" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<p>TODO</p>
<h2 id="servicestack">ServiceStack<a class="headerlink" href="#servicestack" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<p>TODO</p>

                    </div>
                </div>
                <footer>
                    <hr />
                    <div role="contentinfo">
The ORM Cookbook. <a href='https://github.com/Grauenwolf/DotNet-ORM-Cookbook' target='_blank'>Visit us at GitHub</a>.
                    </div>
                </footer>
            </div>
        </section>
    </div>
    <script src="js/jquery-2.1.1.min.js"></script>
    <script src="js/modernizr-2.8.3.min.js"></script>
    <script src="js/highlight.pack.js"></script>
    <script src="js/theme.js"></script>

</body>
</html>
